﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace InventoryManager
{
    public partial class MerchandiseSummaryUserControl : UserControl
    {
        private string _connectionString;
        private MySqlConnection _connection;
        private MySqlDataAdapter _mySqlDataAdapter;

        private const string COLUMN_1_NAME = "ID";
        private const string COLUMN_2_NAME = "Date";
        private const string COLUMN_3_NAME = "Buy";
        private const string COLUMN_4_NAME = "Sell";
        private const string COLUMN_5_NAME = "ItemsRemain";
        private const string COLUMN_6_NAME = "SellerID";

        public MerchandiseSummaryUserControl(string connectionString, int width, int height)
        {
            InitializeComponent();

            _connectionString = connectionString;
            _connection = new MySqlConnection(_connectionString);

            this.Width = width;
            this.Height = height;

            LoadProductName();
        }

        private void OpenReport(DataTable dt, string month, string year, 
            string productName, string dimension, string unitName)
        {
            ShowReportForm showReportForm = new ShowReportForm(_connectionString);
            showReportForm.LoadMerchandiseSummaryReport(dt, month, year, productName, dimension, unitName);
            showReportForm.ShowDialog();
        }

        private void ConfigUnitRadioButton(string selectedProduct, string selectedDimension)
        {
            string query = "SELECT Classifier, PricePerWeight, PricePerItem FROM product WHERE Name = \'" + selectedProduct + "\' " +
                "AND Dimension = \'" + selectedDimension + "\'";

            MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query, _connection);

            DataTable dataTable = new DataTable();
            mySqlDataAdapter.Fill(dataTable);

            string classifier = dataTable.Rows[0][0].ToString();
            string pricePerWeight = dataTable.Rows[0][1].ToString();
            string pricePerItem = dataTable.Rows[0][2].ToString();
            
            ClassifierRadioButton.Text = classifier;

            if (pricePerItem == "")
            {
                KgRadioButton.Enabled = true;
                ClassifierRadioButton.Enabled = false;
                KgRadioButton.Checked = true;
                KgRadioButton_CheckedChanged(null, null);
            }
            else if (pricePerWeight == "")
            {
                KgRadioButton.Enabled = false;
                ClassifierRadioButton.Enabled = true;
                ClassifierRadioButton.Checked = true;
                ClassifierRadioButton_CheckedChanged(null, null);
            }
            else
            {
                KgRadioButton.Enabled = true;
                ClassifierRadioButton.Enabled = true;
                ClassifierRadioButton.Checked = true;
            }
        }

        private void LoadProductName()
        {
            try
            {
                _connection.Open();

                string query = "SELECT DISTINCT Name FROM product";

                MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query, _connection);

                DataTable dataTable = new DataTable();
                mySqlDataAdapter.Fill(dataTable);

                ProductNameComboBox.Items.Clear();
                DimensionComboBox.Items.Clear();

                foreach (DataRow row in dataTable.Rows)
                    ProductNameComboBox.Items.Add(row.ItemArray[0].ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                _connection.Close();
            }
        }

        private DataTable LoadDataFromIDTable(bool invoiceMode)
        {
            _connection.Open();

            string fromDate = FromDateTimePicker.Value.ToString("yyyy-MM-dd") + " 00:00:00";
            string toDate = ToDateTimePicker.Value.ToString("yyyy-MM-dd") + " 23:59:59";

            string query;

            if (invoiceMode)
                query = "SELECT InvoiceID, Date FROM invoiceidcustomername WHERE Date BETWEEN \'" +
                    fromDate + "\' AND \'" + toDate + "\' ORDER BY Date DESC";
            else
                query = "SELECT OrderID, SellerOrderID, SellerDate FROM orderidsellername WHERE SellerDate BETWEEN \'" +
                    fromDate + "\' AND \'" + toDate + "\' ORDER BY SellerDate DESC";

            _mySqlDataAdapter = new MySqlDataAdapter(query, _connection);

            DataTable srcDataTable = new DataTable();
            _mySqlDataAdapter.Fill(srcDataTable);

            DataTable dstDataTable = new DataTable();

            if (!invoiceMode)
                dstDataTable.Columns.Add(COLUMN_6_NAME);
            dstDataTable.Columns.Add(COLUMN_1_NAME);
            dstDataTable.Columns.Add(COLUMN_2_NAME);

            foreach (DataRow dtRow in srcDataTable.Rows)
            {
                DataRow newDtRow = dstDataTable.NewRow();
                int idx = 0;

                if (!invoiceMode)
                {
                    newDtRow[idx] = dtRow[idx].ToString();
                    idx++;
                }
                newDtRow[idx] = dtRow[idx].ToString();
                idx++;
                newDtRow[idx] = dtRow[idx].ToString();

                dstDataTable.Rows.Add(newDtRow);
            }

            _connection.Close();

            return dstDataTable;
        }

        private DataTable GenerateSummaryRows(DataTable rangeDataTable, bool invoiceMode, bool useItemUnit, 
            string productName, string dimension)
        {
            _connection.Open();

            DataTable tmpDataTable = new DataTable();
            DataTable dstDataTable = new DataTable();

            dstDataTable.Columns.Add(COLUMN_1_NAME);
            dstDataTable.Columns.Add(COLUMN_2_NAME);
            dstDataTable.Columns.Add(COLUMN_3_NAME);
            dstDataTable.Columns.Add(COLUMN_4_NAME);

            foreach (DataRow dtRow in rangeDataTable.Rows)
            {
                string query;
                if (invoiceMode)
                {
                    if(useItemUnit)
                        query = "SELECT Quantity FROM invoiceiddescription WHERE InvoiceID = \'" +
                            dtRow[0] + "\' AND ProductName = \'" + productName + "\' AND Dimension = \'" + dimension + "\'";
                    else
                        query = "SELECT Weight FROM invoiceiddescription WHERE InvoiceID = \'" +
                            dtRow[0] + "\' AND ProductName = \'" + productName + "\' AND Dimension = \'" + dimension + "\'";
                }
                else
                {
                    if(useItemUnit)
                        query = "SELECT Quantity FROM orderiddescription WHERE OrderID = \'" +
                            dtRow[0] + "\' AND ProductName = \'" + productName + "\' AND Dimension = \'" + dimension + "\'";
                    else
                        query = "SELECT Weight FROM orderiddescription WHERE OrderID = \'" +
                            dtRow[0] + "\' AND ProductName = \'" + productName + "\' AND Dimension = \'" + dimension + "\'";
                }

                _mySqlDataAdapter = new MySqlDataAdapter(query, _connection);
                tmpDataTable.Clear();
                _mySqlDataAdapter.Fill(tmpDataTable);

                if(tmpDataTable.Rows.Count > 0)
                {
                    foreach(DataRow tmpDtRow in tmpDataTable.Rows)
                    {
                        DataRow newDtRow = dstDataTable.NewRow();

                        if (invoiceMode)
                        {
                            newDtRow[0] = dtRow[0];
                            newDtRow[1] = dtRow[1];
                            newDtRow[2] = tmpDtRow[0];
                            newDtRow[3] = 1;
                        }
                        else
                        {
                            newDtRow[0] = dtRow[1];
                            newDtRow[1] = dtRow[2];
                            newDtRow[2] = tmpDtRow[0];
                            newDtRow[3] = 0;
                        }

                        dstDataTable.Rows.Add(newDtRow);
                    }
                }
            }

            _connection.Close();

            return dstDataTable;
        }

        private DataTable MergeSummaryOrderAndInvoiceRows(DataTable summaryOrder, DataTable summaryInvoice, double itemsRemain)
        {
            DataTable mergedSummary = new DataTable();

            mergedSummary.Columns.Add(COLUMN_1_NAME);
            mergedSummary.Columns.Add(COLUMN_2_NAME);
            mergedSummary.Columns.Add(COLUMN_3_NAME);
            mergedSummary.Columns.Add(COLUMN_4_NAME);
            mergedSummary.Columns.Add(COLUMN_5_NAME);

            int i = 0, j = 0;

            while (i < summaryOrder.Rows.Count || j < summaryInvoice.Rows.Count)
            {
                DataRow newDtRow = mergedSummary.NewRow();

                if (i == summaryOrder.Rows.Count)
                {
                    // add invoice data
                    int invoiceID = Int32.Parse(summaryInvoice.Rows[j][0].ToString());
                    newDtRow[COLUMN_1_NAME] = invoiceID.ToString("D6");
                    newDtRow[COLUMN_2_NAME] = ThaiWordsGenerator.ToThaiDateAbbr(
                        DateTime.Parse(summaryInvoice.Rows[j][1].ToString()));
                    newDtRow[COLUMN_3_NAME] = "-";
                    newDtRow[COLUMN_4_NAME] = summaryInvoice.Rows[j][2];
                    newDtRow[COLUMN_5_NAME] = itemsRemain;

                    itemsRemain += Double.Parse(summaryInvoice.Rows[j][2].ToString());

                    j++;
                }
                else if (j == summaryInvoice.Rows.Count)
                {
                    // add order data
                    newDtRow[COLUMN_1_NAME] = summaryOrder.Rows[i][0];
                    newDtRow[COLUMN_2_NAME] = ThaiWordsGenerator.ToThaiDateAbbr(
                        DateTime.Parse(summaryOrder.Rows[i][1].ToString()));
                    newDtRow[COLUMN_3_NAME] = summaryOrder.Rows[i][2];
                    newDtRow[COLUMN_4_NAME] = "-";
                    newDtRow[COLUMN_5_NAME] = itemsRemain;

                    itemsRemain -= Double.Parse(summaryOrder.Rows[i][2].ToString());

                    i++;
                }
                else
                {
                    DateTime orderDate = DateTime.Parse(summaryOrder.Rows[i][1].ToString());
                    DateTime invoiceDate = DateTime.Parse(summaryInvoice.Rows[j][1].ToString());

                    if (DateTime.Compare(orderDate, invoiceDate) > 0)
                    {
                        // add order data
                        newDtRow[COLUMN_1_NAME] = summaryOrder.Rows[i][0];
                        newDtRow[COLUMN_2_NAME] = ThaiWordsGenerator.ToThaiDateAbbr(
                            DateTime.Parse(summaryOrder.Rows[i][1].ToString()));
                        newDtRow[COLUMN_3_NAME] = summaryOrder.Rows[i][2];
                        newDtRow[COLUMN_4_NAME] = "-";
                        newDtRow[COLUMN_5_NAME] = itemsRemain;

                        itemsRemain -= Double.Parse(summaryOrder.Rows[i][2].ToString());

                        i++;
                    }
                    else
                    {
                        // add invoice data
                        int invoiceID = Int32.Parse(summaryInvoice.Rows[j][0].ToString());
                        newDtRow[COLUMN_1_NAME] = invoiceID.ToString("D6");
                        newDtRow[COLUMN_2_NAME] = ThaiWordsGenerator.ToThaiDateAbbr(
                            DateTime.Parse(summaryInvoice.Rows[j][1].ToString()));
                        newDtRow[COLUMN_3_NAME] = "-";
                        newDtRow[COLUMN_4_NAME] = summaryInvoice.Rows[j][2];
                        newDtRow[COLUMN_5_NAME] = itemsRemain;

                        itemsRemain += Double.Parse(summaryInvoice.Rows[j][2].ToString());

                        j++;
                    }
                }

                mergedSummary.Rows.Add(newDtRow);
            }

            DataRow lastDtRow = mergedSummary.NewRow();
            lastDtRow[COLUMN_1_NAME] = "ยอดยกมา";
            lastDtRow[COLUMN_2_NAME] = ThaiWordsGenerator.ToThaiDateAbbr(FromDateTimePicker.Value);
            lastDtRow[COLUMN_3_NAME] = itemsRemain;
            lastDtRow[COLUMN_4_NAME] = "-";
            lastDtRow[COLUMN_5_NAME] = itemsRemain;
            mergedSummary.Rows.Add(lastDtRow);

            if (CustomRadioButton.Checked)
            {
                int rowCount = mergedSummary.Rows.Count;

                itemsRemain = (double)InitValueNumericUpDown.Value;

                mergedSummary.Rows[rowCount - 1][COLUMN_3_NAME] = itemsRemain;
                mergedSummary.Rows[rowCount - 1][COLUMN_5_NAME] = itemsRemain;

                for(i = mergedSummary.Rows.Count - 2; i >= 0; i--)
                {
                    DataRow dtRow = mergedSummary.Rows[i];
                    if(dtRow[COLUMN_3_NAME].ToString() == "-")
                    {
                        itemsRemain -= Double.Parse(dtRow[COLUMN_4_NAME].ToString());
                        dtRow[COLUMN_5_NAME] = itemsRemain;
                    }
                    else
                    {
                        itemsRemain += Double.Parse(dtRow[COLUMN_3_NAME].ToString());
                        dtRow[COLUMN_5_NAME] = itemsRemain;
                    }
                }
            }

            if (ClassifierRadioButton.Checked)
            {
                foreach (DataRow dtRow in mergedSummary.Rows)
                {
                    for(int col = 2; col <= 4; col++)
                    {
                        if (dtRow[col].ToString() != "-")
                        {
                            int number = Int32.Parse(dtRow[col].ToString());
                            dtRow[col] = number.ToString("N0");
                        }
                    }
                }
            }
            else
            {
                foreach (DataRow dtRow in mergedSummary.Rows)
                {
                    for (int col = 2; col <= 4; col++)
                    {
                        if (dtRow[col].ToString() != "-")
                        {
                            double number = Double.Parse(dtRow[col].ToString());
                            //dtRow[col] = String.Format("{0:0,0.###}", number);
                            dtRow[col] = String.Format("{0:#,0.###}", number);
                        }
                    }
                }
            }

            return mergedSummary;
        }

        private void GenerateReportButton_Click(object sender, EventArgs e)
        {
            try 
            {
                DataTable dataFromOrderIDSellerName = LoadDataFromIDTable(false);
                DataTable dataFromInvoiceIDCustomerName = LoadDataFromIDTable(true);

                string productName = ProductNameComboBox.Items[ProductNameComboBox.SelectedIndex].ToString();
                string dimension = DimensionComboBox.Items[DimensionComboBox.SelectedIndex].ToString();

                bool useItemUnit = ClassifierRadioButton.Checked;

                DataTable summaryOrder = GenerateSummaryRows(dataFromOrderIDSellerName, false, useItemUnit, productName, dimension);
                DataTable summaryInvoice = GenerateSummaryRows(dataFromInvoiceIDCustomerName, true, useItemUnit, productName, dimension);

                if (summaryOrder.Rows.Count == 0 && summaryInvoice.Rows.Count == 0)
                {
                    ConfirmOpenNullReportForm nullReportForm = new ConfirmOpenNullReportForm();
                    if (nullReportForm.ShowDialog() != DialogResult.OK)
                        return;
                }

                _connection.Open();

                DataTable tmpDataTable = new DataTable();

                string query = "SELECT Classifier, ItemsRemainVat FROM product WHERE Name = \'" + productName
                + "\' AND Dimension = \'" + dimension + "\'";
                _mySqlDataAdapter = new MySqlDataAdapter(query, _connection);
                _mySqlDataAdapter.Fill(tmpDataTable);

                string unitName;

                if (ClassifierRadioButton.Checked)
                    unitName = tmpDataTable.Rows[0][0].ToString();
                else
                    unitName = "กิโลกรัม";

                double itemsRemain = Double.Parse(tmpDataTable.Rows[0][1].ToString());

                _connection.Close();

                DataTable mergedSummary = MergeSummaryOrderAndInvoiceRows(summaryOrder, summaryInvoice, itemsRemain);

                DateTime startDate = FromDateTimePicker.Value;
                DateTime endDate = ToDateTimePicker.Value;

                string month = "", year = "";

                if (startDate.Year == endDate.Year)
                {
                    year = ThaiWordsGenerator.ToThaiYear(startDate);
                    if (startDate.Month == endDate.Month)
                        month = ThaiWordsGenerator.ToThaiMonth(startDate);
                    else
                        month = ThaiWordsGenerator.ToThaiMonth(startDate) + " - " + ThaiWordsGenerator.ToThaiMonth(endDate);
                }
                else
                {
                    year = ThaiWordsGenerator.ToThaiYear(startDate) + " - " + ThaiWordsGenerator.ToThaiYear(endDate);
                    month = ThaiWordsGenerator.ToThaiMonth(startDate) + " - " + ThaiWordsGenerator.ToThaiMonth(endDate);
                }

                OpenReport(mergedSummary, month, year, productName, dimension, unitName);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                _connection.Close();
            }
        }

        private void ProductNameComboBox_SelectionChangeCommitted(object sender, EventArgs e)
        {
            try
            {
                string selectedProduct = ProductNameComboBox.Items[ProductNameComboBox.SelectedIndex].ToString();

                _connection.Open();

                string query = "SELECT Dimension FROM product WHERE Name = \'" + selectedProduct + "\' ORDER BY Dimension";

                MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query, _connection);

                DataTable dataTable = new DataTable();
                mySqlDataAdapter.Fill(dataTable);

                DimensionComboBox.Items.Clear();

                foreach (DataRow row in dataTable.Rows)
                    DimensionComboBox.Items.Add(row.ItemArray[0].ToString());

                DimensionComboBox.SelectedIndex = 0;

                string selectedDimension = DimensionComboBox.Items[0].ToString();

                ConfigUnitRadioButton(selectedProduct, selectedDimension);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                GenerateReportButton.Visible = true;
                _connection.Close();
            }
        }

        private void DimensionComboBox_SelectionChangeCommitted(object sender, EventArgs e)
        {
            try
            {
                string selectedProduct = ProductNameComboBox.Items[ProductNameComboBox.SelectedIndex].ToString();
                string selectedDimension = DimensionComboBox.Items[DimensionComboBox.SelectedIndex].ToString();

                _connection.Open();

                ConfigUnitRadioButton(selectedProduct, selectedDimension);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                _connection.Close();
            }
        }

        private void FromItemsRemainRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            InitValueNumericUpDown.Visible = !FromItemsRemainRadioButton.Checked;
        }

        private void CustomRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            InitValueNumericUpDown.Visible = !FromItemsRemainRadioButton.Checked;
        }

        private void ClassifierRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            if (ClassifierRadioButton.Checked)
            {
                FromItemsRemainRadioButton.Enabled = true;
                InitValueNumericUpDown.Increment = Convert.ToDecimal(1);
            }
            else
            {
                FromItemsRemainRadioButton.Enabled = false;
                InitValueNumericUpDown.Increment = Convert.ToDecimal(0.001);
                CustomRadioButton.Checked = true;
            }
        }

        private void KgRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            if (KgRadioButton.Checked)
            {
                FromItemsRemainRadioButton.Enabled = false;
                InitValueNumericUpDown.Increment = Convert.ToDecimal(0.001);
                CustomRadioButton.Checked = true;
            }
            else
            {
                FromItemsRemainRadioButton.Enabled = true;
                InitValueNumericUpDown.Increment = Convert.ToDecimal(1);
            }
        }
    }
}
